﻿using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace WebShopData
{
    public class OrderRow
    {
        public OrderRow(int orderId, int quantity, Article article, string status = "Active", int orderRowId = 0)
        {
            OrderRowId = orderRowId;
            OrderId = orderId;
            Quantity = quantity;
            Article = article;
            Status = status;
        }

        public int OrderRowId { get; set; }
        public int OrderId { get; set; }
        public Article Article { get; private set; }
        public int Quantity { get; set; }
        public string Status { get; set; }
        public long Sum
        {
            get
            {
                return Quantity * Article.UnitPrice;
            }
        }

        public override string ToString()
        {
            string str = string.Format("{0}x {1}", Quantity, Article.ArticleName);
            return str;
        }
    }

    public static class OrderRowDB
    {
        public static int InsertNewOrderRow(OrderRow orderRow)
        {
            using (SqlConnection connection = GalaxySale2014DB.GetConnection())
            {
                using (SqlCommand command = connection.CreateCommand())
                {
                    var sqlQuery = new StringBuilder("INSERT INTO [dbo].[OrderRows]([OrderId],[Quantity],[ArticleId],[Status])");
                    sqlQuery.Append(" VALUES(@OrderId, @Quantity, @ArticleId, @Status)");
                    sqlQuery.Append(" SELECT @@IDENTITY");
                    command.CommandText = sqlQuery.ToString();

                    command.Parameters.Add("OrderId", SqlDbType.Int).Value = orderRow.OrderId;
                    command.Parameters.Add("Quantity", SqlDbType.Int).Value = orderRow.Quantity;
                    command.Parameters.Add("ArticleId", SqlDbType.Int).Value = orderRow.Article.ArticleId;
                    command.Parameters.Add("Status", SqlDbType.VarChar, 50).Value = orderRow.Status;

                    int identity = int.Parse(command.ExecuteScalar().ToString());
                    return identity;
                }
            }
        }

        public static List<OrderRow> GetAllOrderRows()
        {
            return GetOrderRowsBase("", new List<SqlParameter>());
        }

        public static List<OrderRow> GetOrderRowsByOrderId(int orderId)
        {
            string whereLine = " WHERE [OrderId] = @OrderId";
            List<SqlParameter> parameters = new List<SqlParameter>();
            SqlParameter sqlParameter = new SqlParameter("OrderId", SqlDbType.Int);
            sqlParameter.Value = orderId;
            parameters.Add(sqlParameter);
            return GetOrderRowsBase(whereLine, parameters);
        }

        public static OrderRow GetOrderRowById(int orderRowId)
        {
            string whereLine = " WHERE [OrderRowId] = @OrderRowId";
            List<SqlParameter> parameters = new List<SqlParameter>();
            SqlParameter sqlParameter = new SqlParameter("OrderRowId", SqlDbType.Int);
            sqlParameter.Value = orderRowId;
            parameters.Add(sqlParameter);
            List<OrderRow> orderRows = GetOrderRowsBase(whereLine, parameters);
            if (orderRows.Count == 1)
            {
                return orderRows[0];
            }
            return null;
        }

        public static List<OrderRow> GetOrderRowsBase(string whereLine, List<SqlParameter> parameters)
        {
            DataSet dataSetOrderRows = GetOrderRowsAsDataSet(whereLine, parameters);
            var orderRows = new List<OrderRow>();
            foreach (DataRow row in dataSetOrderRows.Tables[0].Rows)
            {
                orderRows.Add(new OrderRow(
                    orderId: int.Parse(row["Orderid"].ToString().Trim()),
                    quantity: int.Parse(row["Quantity"].ToString().Trim()),
                    article: ArticleDB.GetArticleById(int.Parse(row["ArticleId"].ToString().Trim())),
                    status: row["Status"].ToString().Trim(),
                    orderRowId: int.Parse(row["OrderRowId"].ToString().Trim())));
            }
            return orderRows;
        }

        public static DataSet GetOrderRowsAsDataSet(string whereLine, List<SqlParameter> parameters)
        {
            using (SqlConnection connection = GalaxySale2014DB.GetConnection())
            {
                using (SqlCommand command = connection.CreateCommand())
                {
                    var sqlQuery = new StringBuilder("SELECT [OrderRowId],[OrderId],[Quantity],[ArticleId],[Status]");
                    sqlQuery.Append(" FROM [dbo].[OrderRowsNotDeletedView]");
                    sqlQuery.Append(whereLine);
                    command.CommandText = sqlQuery.ToString();
                    foreach (SqlParameter sqlParameter in parameters)
                    {
                        command.Parameters.Add(sqlParameter);
                    }
                    DataSet result = GalaxySale2014DB.GetDataSet(command);
                    return result;
                }
            }
        }

        public static int UpdateOrderRow(OrderRow orderRow)
        {
            using (SqlConnection connection = GalaxySale2014DB.GetConnection())
            {
                using (SqlCommand command = connection.CreateCommand())
                {
                    var sqlQuery = new StringBuilder("UPDATE [dbo].[OrderRows]");
                    sqlQuery.Append(" SET [OrderId] = @OrderId");
                    sqlQuery.Append(", [Quantity] = @Quantity");
                    sqlQuery.Append(", [ArticleId] = @ArticleId");
                    sqlQuery.Append(", [Status] = @Status");
                    sqlQuery.Append(" WHERE [OrderRowId] = @OrderRowId");
                    command.CommandText = sqlQuery.ToString();

                    command.Parameters.Add("OrderId", SqlDbType.Int).Value = orderRow.OrderId;
                    command.Parameters.Add("Quantity", SqlDbType.Int).Value = orderRow.Quantity;
                    command.Parameters.Add("ArticleId", SqlDbType.Int).Value = orderRow.Article.ArticleId;
                    command.Parameters.Add("Status", SqlDbType.VarChar, 50).Value = orderRow.Status;
                    command.Parameters.Add("OrderRowId", SqlDbType.Int).Value = orderRow.OrderRowId;

                    int affectedRows = command.ExecuteNonQuery();
                    return affectedRows;
                }
            }
        }

        public static int DeleteOrderRowById(int orderRowId)
        {
            OrderRow orderRow = GetOrderRowById(orderRowId);
            orderRow.Status = "Deleted";
            return UpdateOrderRow(orderRow);
            //using (SqlConnection connection = GalaxySale2014DB.GetConnection())
            //{
            //    using (SqlCommand command = connection.CreateCommand())
            //    {
            //        var sqlQuery = new StringBuilder("DELETE FROM [dbo].[OrderRows]");
            //        sqlQuery.Append("WHERE [OrderRowId] = @OrderRowId");
            //        command.CommandText = sqlQuery.ToString();

            //        command.Parameters.Add("OrderRowId", SqlDbType.Int).Value = orderRowId;

            //        int affectedRows = command.ExecuteNonQuery();
            //        return affectedRows;
            //    }
            //}
        }
    }
}